ALTER proc sp_insert_quotation_lineItem_proc
(
	@quotation_id 	int, 
	@lineItem_no 	int, 
	@product_id 	int, 
	@lineItem_Desc 	varchar(500), 
	@qty 		int,
	@unit_price 	float,
	@amount 	float,
	@requested_date datetime, 
	@user_id 	varchar(50),
	@is_taxable 	int, 
	@po_lineItem_no int, 
	@quoted_qty 	int,
	@lead_time	varchar(50),
	@quoted_date	datetime,
	@is_only_new_lineitem_change int,
	@status 	int,
	@customer_partnumber varchar(50),
	@REQUESTED_PRICE FLOAT,
	@CUST_RESALE_PRICE FLOAT,
	@SUGG_RESALE_PRICE FLOAT,
	@IS_NEW_CUSTOMER_PART_NUMBER INT, --0 -TRUE 1 -FALSE
	@MIN_ORDER_QTY INT
)
AS
BEGIN	
	DECLARE @lineItem_ID int
	DECLARE @CUSTOMER_ID INT
	
	SELECT @CUSTOMER_ID = CUSTOMER_ID FROM QUOTATION_TABLE WHERE Quotation_ID = @quotation_id
	
	IF ( @IS_NEW_CUSTOMER_PART_NUMBER IS NOT NULL AND @IS_NEW_CUSTOMER_PART_NUMBER = 0 AND LEN(LTRIM(RTRIM(@customer_partnumber))) > 0 )
	 BEGIN
		IF ( (SELECT COUNT(*) CNT FROM CUSTOMER_PARTNUMBERS WHERE CUSTOMER_ID = @CUSTOMER_ID AND PRODUCT_ID = @product_id AND CUSTOMER_PARTNUMBER = LTRIM(RTRIM(@customer_partnumber)) AND ODM_ID IS NULL) = 0 )
		 BEGIN
			INSERT INTO CUSTOMER_PARTNUMBERS( CUSTOMER_ID, PRODUCT_ID, CUSTOMER_PARTNUMBER, LABEL_NUMBER ) VALUES( @CUSTOMER_ID, @product_id, LTRIM(RTRIM(@customer_partnumber)), '' )
	 	 END
	 END

	INSERT INTO Quotation_Order_LineItem ( Quotation_ID, QLI_Line_Item_No, Product_ID, QLI_Description, QLI_Qty, QLI_Lead_Time, QLI_Unit_Cost, QLI_Amount, QLI_Requested_Date, QLI_Booking_Date, QLI_Status, QLI_Added_By_ID, QLI_Added_By_Name, QLI_Taxable , QLI_PO_Line_Item_No, TimeStamp,QLI_Quoted_Qty,QLI_Quoted_Date, QLI_Customer_PartNumber, REQUESTED_PRICE, CUST_RESALE_PRICE, SUGG_RESALE_PRICE, QLI_ACTUAL_QTY, MIN_ORDER_QTY )
	VALUES ( @quotation_id, @lineItem_no, @product_id, @lineItem_Desc, @qty, @lead_time,  @unit_price, @amount, @requested_date, CURRENT_TIMESTAMP,'HOLD',@user_id, @user_id, @is_taxable, @po_lineItem_no, CURRENT_TIMESTAMP, @quoted_qty , @quoted_date,  @customer_partnumber, @REQUESTED_PRICE, @CUST_RESALE_PRICE, @SUGG_RESALE_PRICE, @qty, @MIN_ORDER_QTY )

	SELECT @lineItem_ID = (SELECT @@IDENTITY)
	
	
	/* If this is first line item then it will have duplicate copy into Quotation_Change_Order_LineItem*/
	INSERT Quotation_Change_Order_LineItem ( LineItem_ID, Quotation_ID, Change_Number, Line_Item_No, Product_ID, Qty, Lead_Time, Unit_Cost, Amount, Requested_Date, Booking_Date, Status, Change_By_ID, Change_By_Name, Taxable, PO_Line_Item_No, TimeStamp, Transfer, Is_Approved, Quoted_Qty, Quoted_Date, REQUESTED_PRICE, CUST_RESALE_PRICE, SUGG_RESALE_PRICE, QLI_ACTUAL_QTY, MIN_ORDER_QTY )
	VALUES ( @lineItem_ID, @quotation_id, 0, @lineItem_no, @product_id, @qty, @lead_time, @unit_price, @amount, @requested_date, CURRENT_TIMESTAMP, 'HOLD', @user_id, @user_id, @is_taxable, @po_lineItem_no, CURRENT_TIMESTAMP, 1, 1, @quoted_qty, @quoted_date, @REQUESTED_PRICE, @CUST_RESALE_PRICE, @SUGG_RESALE_PRICE, @qty, @MIN_ORDER_QTY) 
	
	IF ( @is_only_new_lineitem_change <> 0 and @is_only_new_lineitem_change is not null )
	BEGIN		
		IF (@is_only_new_lineitem_change = 1 and @status = 1)
		BEGIN
			DECLARE @db_revision int
			SELECT @db_revision =  Q_Revision FROM QUOTATION_TABLE WHERE Quotation_ID = @Quotation_ID  
			IF ( @db_revision IS NULL )
			BEGIN
				SET @db_revision = 0				
			END
			UPDATE QUOTATION_TABLE SET Q_Revision = @db_revision + 1 WHERE Quotation_ID = @Quotation_ID		
		END
	END

END
